﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLite.NET;

namespace BierdopjeTV.Database
{
    class GUISeasons
    {
        public int Season;
        public int Episodes;
        public int SubsNL;
        public int SubsEN;
        public int Watched;
        public int Downloaded;

    }

    static class DBSeasons
    {

        public static List<GUISeasons> getSeasons(int ShowID)
        {
            Helpers.Logger.WriteSimpel("DBSeasons.getSeasons()");
            List<GUISeasons> _Seasons = new List<GUISeasons>();

            GUISeasons _Season;

            string _Query;

            _Query = "";
            _Query += "SELECT" + Environment.NewLine;
            _Query += "	(SELECT Sum(SubENDownloaded) FROM BierdopjeEpisodes WHERE ShowID = a.ShowID AND Season = a.Season AND Downloaded = 1) AS SubEN," + Environment.NewLine;
            _Query += "	(SELECT Sum(SubNLDownloaded) FROM BierdopjeEpisodes WHERE ShowID = a.ShowID AND Season = a.Season AND Downloaded = 1 ) AS SubNL," + Environment.NewLine;
            _Query += "	ShowName," + Environment.NewLine;
            _Query += "	a.ShowID," + Environment.NewLine;
            _Query += "	Season," + Environment.NewLine;
            _Query += "	Sum (Watched) As Gezien," + Environment.NewLine;
            _Query += "	Sum(Downloaded) AS Gedownload" + Environment.NewLine;
            _Query += "" + Environment.NewLine;
            _Query += "FROM" + Environment.NewLine;
            _Query += "	BierdopjeEpisodes as a" + Environment.NewLine;
            _Query += "LEFT JOIN" + Environment.NewLine;
            _Query += "	BierdopjeShows ON BierdopjeShows.ShowID = a.ShowID" + Environment.NewLine;
            _Query += "WHERE" + Environment.NewLine;
            _Query += "	a.ShowID = '" + ShowID.ToString() + "'" + Environment.NewLine;
            if (Database.getSettingValue("AlleenLokaleEpisodes") == "True")
            {
                _Query += "	AND" + Environment.NewLine;
                _Query += "	Downloaded = 1" + Environment.NewLine;
            } 
            _Query += "GROUP BY" + Environment.NewLine;
            _Query += "	Season" + Environment.NewLine;
            _Query += "ORDER BY" + Environment.NewLine;
            _Query += "	Season ASC" + Environment.NewLine;

            SQLiteResultSet _Result = Helpers.SqliteHelper.ExecuteQuery(_Query);

            for (int i = 0; i < _Result.Rows.Count; i++)
            {
                _Season = new GUISeasons();
                _Season.Season = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Season"]]);
                Helpers.Logger.WriteSimpel("DBSeasons.getSeasons() ShowID : " + ShowID.ToString() + "  -> Season " + _Season.Season.ToString());
                _Season.Episodes = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Gedownload"]]);
                try
                {
                    _Season.SubsNL = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubNL"]]);
                    _Season.SubsEN = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["SubEN"]]);
                }
                catch { }
                _Season.Watched = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Gezien"]]);
                _Season.Downloaded = Convert.ToInt32(_Result.Rows[i].fields[(int)_Result.ColumnIndices["Gedownload"]]);

                _Seasons.Add(_Season);
                _Season = null;

            }

            return _Seasons;

        }
    }
}
